using CodeSmith.Engine;
using SchemaExplorer;
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Text;
using System.Text.RegularExpressions;
using CodeSmith.BaseTemplates;
namespace zfbbs.Templates
{
	
	
	/// <summary>
	/// Common code-behind class used to simplify SQL Server based CodeSmith templates
	/// </summary>
	public class CommonCode : CodeTemplate
	{
		
#region  CommonCode

public string GetMemberVariableDeclarationStatement(ColumnSchema column)
{
	return GetMemberVariableDeclarationStatement("protected", column);
}

public string GetDescription(ColumnSchema column)
{
	return column.Description.Replace("\r\n"," ");
}
public string GetMemberVariableDeclarationStatement(string protectionLevel, ColumnSchema column)
{
	string statement = protectionLevel + " ";
	statement += GetCSharpVariableType(column) + " " + GetMemberVariableName(column);
	
	string defaultValue = GetMemberVariableDefaultValue(column);
	if (defaultValue != "")
	{
		statement += " = " + defaultValue;
	}
	
	statement += ";"+(column.Description.Length>0?"\t\t//"+GetDescription(column):""); 
	
	return statement;
}



public string GetCamelCaseName(string value)
{
	return value.Substring(0, 1).ToLower() + value.Substring(1);
}

public string GetMemberVariableName(ColumnSchema column)
{
	string propertyName = GetPropertyName(column);
	string memberVariableName = "_" + GetCamelCaseName(propertyName);
	
	return memberVariableName;
}
public string GetMemberVariableNameSimple(ColumnSchema column)
{
	string propertyName = GetPropertyName(column);
	string memberVariableName =  GetCamelCaseName(propertyName);
	
	return memberVariableName;
}
public string GetPropertyName(ColumnSchema column)
{
	string propertyName = column.Name;
	
	//if (propertyName == column.Table.Name + "Name") return "Name";
	//if (propertyName == column.Table.Name + "Description") return "Description";
	
	if (propertyName.EndsWith("TypeCode")) propertyName = propertyName.Substring(0, propertyName.Length - 4);
	
	return propertyName;
}
		
public string GetMemberVariableDefaultValue(ColumnSchema column)
{
	switch (column.DataType)
	{
		case DbType.Guid:
		{
			return "Guid.Empty";
		}
		case DbType.AnsiString:
		case DbType.AnsiStringFixedLength:
		case DbType.String:
		case DbType.StringFixedLength:
		{
			return "\"\"";
		}
		default:
		{
			return "";
		}
	}
}

public string GetCSharpVariableType(ColumnSchema column)
{
	if (column.Name.EndsWith("TypeCode")) return column.Name;
	
	switch (column.DataType)
	{
		case DbType.AnsiString: return "string";
		case DbType.AnsiStringFixedLength: return "string";
		case DbType.Binary: return "byte[]";
		case DbType.Boolean: return "bool";
		case DbType.Byte: return "byte";
		case DbType.Currency: return "decimal";
		case DbType.Date: return "DateTime?";
		case DbType.DateTime: return "DateTime?";
		case DbType.Decimal: return "decimal";
		case DbType.Double: return "double";
		case DbType.Guid: return "Guid";
		case DbType.Int16: return "short";
		case DbType.Int32: return "int";
		case DbType.Int64: return "long";
		case DbType.Object: return "object";
		case DbType.SByte: return "sbyte";
		case DbType.Single: return "float";
		case DbType.String: return "string";
		case DbType.StringFixedLength: return "string";
		case DbType.Time: return "TimeSpan";
		case DbType.UInt16: return "ushort";
		case DbType.UInt32: return "uint";
		case DbType.UInt64: return "ulong";
		case DbType.VarNumeric: return "decimal";
		default:
		{
			return "__UNKNOWN__" + column.NativeType;
		}
	}
}

public string GetReaderMethod(ColumnSchema column)
{
	switch (column.DataType)
	{
		case DbType.Byte:
		{
			return "GetByte";
		}
		case DbType.Int16:
		{
			return "GetInt16";
		}
		case DbType.Int32:
		{
			return "GetInt32";
		}
		case DbType.Int64:
		{
			return "GetInt64";
		}
		case DbType.AnsiStringFixedLength:
		case DbType.AnsiString:
		case DbType.String:
		case DbType.StringFixedLength:
		{
			return "GetString";
		}
		case DbType.Boolean:
		{
			return "GetBoolean";
		}
		case DbType.Guid:
		{
			return "GetGuid";
		}
		case DbType.Currency:
		case DbType.Decimal:
		{
			return "GetDecimal";
		}
		case DbType.DateTime:
		case DbType.Date:
		{
			return "GetDateTime";
		}
		case DbType.Binary:
		{
			return "GetBytes";
		}
		default:
		{
			return "__SQL__" + column.DataType;
		}
	}
}
public string GetClassInst(TableSchema table)
{
return GetCamelCaseName(GetClassName(table));
}

public string GetEntityName(TableSchema table,string TablePrefix,bool plural)
{
	string entityName=table.Name;
	if (entityName.StartsWith(TablePrefix))
	{
		entityName = entityName.Substring(TablePrefix.Length);
	}
	if (plural)
	{
		entityName = StringUtility.ToPlural(entityName);
	}
	else
	{
		entityName = StringUtility.ToSingular(entityName);
	}
	return entityName.Substring(0, 1).ToUpper()+entityName.Substring(1);
}
public string GetEntityName(TableSchema table)
{
	return GetEntityName(table,"tb_",false);
}
public string GetEntityName(TableSchema table,bool plural)
{
	return GetEntityName(table,"tb_",plural);
}
public string GetBySuffix(ColumnSchemaCollection columns)
{
    return GetBySuffix(columns,false);
}
public string GetBySuffix(ColumnSchemaCollection columns,bool upcase)
{
    System.Text.StringBuilder bySuffix = new System.Text.StringBuilder();
	for (int i = 0; i < columns.Count; i++)
	{
	    if (i > 0) bySuffix.Append("And");
		if(upcase)
		{
	    	bySuffix.Append(columns[i].Name.Substring(0, 1).ToUpper()+columns[i].Name.Substring(1));
		}else
		{
			bySuffix.Append(columns[i].Name);
		}
	}
	
	return bySuffix.ToString();
}
public string GetClassName(TableSchema table,bool plural)
{
	string entityName=GetEntityName(table);

	if (plural)
	{
		entityName = StringUtility.ToPlural(entityName);
	}
	else
	{
		//entityName = StringUtility.ToSingular(entityName);
	}
	
	return entityName.Substring(0, 1).ToUpper()+entityName.Substring(1);
}
public string GetClassName(TableSchema table)
{
	return GetClassName(table,false);
}
public string GetClassName(TableSchema table,string Ext)
{
	return GetClassName(table)+Ext;
}
public string GetSqlDbType(ColumnSchema column)
{
	switch (column.NativeType)
	{
		case "bigint": return "BigInt";
		case "binary": return "Binary";
		case "bit": return "Bit";
		case "char": return "Char";
		case "datetime": return "DateTime";
		case "decimal": return "Decimal";
		case "float": return "Float";
		case "image": return "Image";
		case "int": return "Int";
		case "money": return "Money";
		case "nchar": return "NChar";
		case "ntext": return "NText";
		case "numeric": return "Decimal";
		case "nvarchar": return "NVarChar";
		case "real": return "Real";
		case "smalldatetime": return "SmallDateTime";
		case "smallint": return "SmallInt";
		case "smallmoney": return "SmallMoney";
		case "sql_variant": return "Variant";
		case "sysname": return "NChar";
		case "text": return "Text";
		case "timestamp": return "Timestamp";
		case "tinyint": return "TinyInt";
		case "uniqueidentifier": return "UniqueIdentifier";
		case "varbinary": return "VarBinary";
		case "varchar": return "VarChar";
		default: return "__UNKNOWN__" + column.NativeType;
	}
}

public string GetPrimaryKeyType(TableSchema table)
{
	if (table.PrimaryKey != null)
	{
		if (table.PrimaryKey.MemberColumns.Count == 1)
		{
			return GetCSharpVariableType(table.PrimaryKey.MemberColumns[0]);
		}
		else
		{
			throw new ApplicationException("This template will not work on primary keys with more than one member column.");
		}
	}
	else
	{
		throw new ApplicationException("This template will only work on tables with a primary key.");
	}
}

public string GetPrimaryArgs(TableSchema table)
{
	string retStr="";
	if (table.HasPrimaryKey)
	{
		foreach(ColumnSchema column in table.PrimaryKey.MemberColumns)
		{
			retStr+=GetCSharpVariableType(column)+" "+column.Name  +",";
		}
		retStr=retStr.Substring(0,retStr.Length-1);
	}
	return retStr;
	
}

public string GetPrimaryArgsValueStr(TableSchema table)
{
	string retStr="";
	if (table.HasPrimaryKey)
	{
		foreach(ColumnSchema column in table.PrimaryKey.MemberColumns)
		{
			retStr+=column.Name+".ToString()+\"_\"" + "+";
		}
		retStr=retStr.Substring(0,retStr.Length-1);
	}
	return retStr;
	
}
public string GetColumnNamesByObj(ColumnSchemaCollection columns, string obj)
{
	string retStr="";
	
		foreach(ColumnSchema column in columns)
		{
			
			retStr+=obj+ "."+column.Name+",";
			
		}
		
		retStr=retStr.Substring(0,retStr.Length-1);
		

	return retStr;
	
}
public string GetNormalColumnNames(ColumnSchemaCollection columns, string obj)
{
	string retStr="";
	
		foreach(ColumnSchema column in columns)
		{
		   
	        //remove Identity
			if( !((column.DataType==DbType.Guid
				||column.DataType==DbType.Int16
				||column.DataType==DbType.Int32
				||column.DataType==DbType.Int64)
				&&((bool)column.ExtendedProperties["CS_IsIdentity"].Value == true))
			)
			{
			retStr+=obj+ "."+column.Name+",";
			}
		}
		
		retStr=retStr.Substring(0,retStr.Length-1);
		

	return retStr;
	
}

public string GetPrimaryNames(TableSchema table)
{
	string retStr="";
	if (table.HasPrimaryKey)
	{
		foreach(ColumnSchema column in table.PrimaryKey.MemberColumns)
		{
			retStr+=column.Name  +",";
		}
		
		retStr=retStr.Substring(0,retStr.Length-1);
		
	}
	return retStr;
	
}
public string GetColumnsNames(ColumnSchemaCollection columns)
{
	    string retStr="";
		foreach(ColumnSchema column in columns)
		{
			retStr+=column.Name  +",";
		}
		
		retStr=retStr.Substring(0,retStr.Length-1);
		
	
	return retStr;
	
}

public string GetColumnsArgs(ColumnSchemaCollection columns)
{
	string retStr="";
	
		foreach(ColumnSchema column in columns)
		{
			retStr+=GetCSharpVariableType(column)+" "+column.Name  +",";
		}
		retStr=retStr.Substring(0,retStr.Length-1);
	return retStr;
	
}

public  bool isStringType(ColumnSchema column)
{
	return column.NativeType.IndexOf("char")>0;
	
}
public string GetPrimaryNamesBySqlArg(TableSchema table)
{
	string retStr="";
	if (table.HasPrimaryKey)
	{
		int i=0;
		foreach(ColumnSchema column in table.PrimaryKey.MemberColumns)
		{   
			if (isStringType(column))
			{
				retStr+=column.Name  +"='{"+i.ToString()+"}' and ";
			}
			else
			{
				retStr+=column.Name  +"={"+i.ToString()+"} and ";
				}
			i++;
		}
		
		retStr=retStr.Substring(0,retStr.Length-4);
		
	}
	return retStr;
	
}

///**********************************************************************
/// <summary>
		/// Return a specified number of tabs
		/// </summary>
		/// <param name="n">Number of tabs</param>
		/// <returns>n tabs</returns>
		public string Tab(int n)
		{
			return new String('\t', n);
		}

		/// <summary>
		/// Get the safe name for a data object by determining if it contains spaces or other illegal
		/// characters - if so wrap with []
		/// </summary>
		/// <param name="schemaObject">Database schema object (e.g. a table, stored proc, etc)</param>
		/// <returns>The safe name of the object</returns>
		public string GetSafeName(SchemaObjectBase schemaObject)
		{
			return GetSafeName(schemaObject.Name);
		}

		/// <summary>
		/// Get the safe name for a data object by determining if it contains spaces or other illegal
		/// characters - if so wrap with []
		/// </summary>
		/// <param name="objectName">The name of the database schema object</param>
		/// <returns>The safe name of the object</returns>
		public string GetSafeName(string objectName)
		{
			return objectName.IndexOfAny(new char[]{' ', '@', '-', ',', '!'}) > -1 ? "[" + objectName + "]" : objectName;
		}

	
		
		/// <summary>
		/// Remove any non-word characters from a SchemaObject's name (word characters are a-z, A-Z, 0-9, _)
		/// so that it may be used in code
		/// </summary>
		/// <param name="schemaObject">DB Object whose name is to be cleaned</param>
		/// <returns>Cleaned up object name</returns>
		public string GetCleanName(SchemaObjectBase schemaObject)
		{
			return GetCleanName(schemaObject.Name);
		}

		/// <summary>
		/// Remove any non-word characters from a name (word characters are a-z, A-Z, 0-9, _)
		/// so that it may be used in code
		/// </summary>
		/// <param name="name">name to be cleaned</param>
		/// <returns>Cleaned up object name</returns>
		public string GetCleanName(string name)
		{
			return Regex.Replace(name, @"[\W]", "");
		}

		/// <summary>
		/// Get the cleaned, camelcased name of a parameter
		/// </summary>
		/// <param name="par">Command Parameter</param>
		/// <returns>the cleaned, camelcased name </returns>
		public string GetCleanParName(ParameterSchema par)
		{
			return GetCleanParName(par.Name);
		}

		/// <summary>
		/// Get the cleaned, camelcased version of a name
		/// </summary>
		/// <param name="name">name to be cleaned</param>
		/// <returns>the cleaned, camelcased name </returns>
		public string GetCleanParName(string name)
		{
			return GetCamelCaseName(GetCleanName(name));
		}

		/// <summary>
		/// Get the member variable styled version of a name
		/// </summary>
		/// <param name="name">name to be cleaned</param>
		/// <returns>the cleaned, camelcased name with a _ prefix</returns>
		public string GetMemberVariableName(string name)
		{
			return "_" + GetCleanParName(name);
		}
		
		/// <summary>
		/// Get the description ext. property of a column and return as inline SQL comment
		/// </summary>
		/// <param name="schemaObject">Any database object, but typically a column</param>
		/// <returns>Object description, as inline SQL comment</returns>
		public string GetColumnSqlComment(SchemaObjectBase schemaObject)
		{
			return schemaObject.Description.Length > 0 ? "-- " + schemaObject.Description : "";
		}

		/// <summary>
		/// Check if a column is an identity column
		/// </summary>
		/// <param name="column">DB table column to be checked</param>
		/// <returns>Identity?</returns>
		public bool IsIdentityColumn(ColumnSchema column)
		{
			return (bool)column.ExtendedProperties["CS_IsIdentity"].Value;
		} 

		/// <summary>
		/// Get the owner of a table
		/// </summary>
		/// <param name="table">The table to check</param>
		/// <returns>The safe name of the owner of the table</returns>
		public string GetOwner(TableSchema table)
		{
			return (table.Owner.Length > 0) ? GetSafeName(table.Owner) + "." : "";
		}

		/// <summary>
		/// Get the owner of a command
		/// </summary>
		/// <param name="command">The command to check</param>
		/// <returns>The safe name of the owner of the command</returns>
		public string GetOwner(CommandSchema command)
		{
			return (command.Owner.Length > 0) ? GetSafeName(command.Owner) + "." : "";
		}

		/// <summary>
		/// Does the command have a resultset?
		/// </summary>
		/// <param name="cmd">Command in question</param>
		/// <returns>Resultset?</returns>
		public bool HasResultset(CommandSchema cmd)
		{
			return cmd.CommandResults.Count > 0;
		}

		/// <summary>
		/// Get a SqlParameter statement for a column
		/// </summary>
		/// <param name="column">Column for which to get the Sql parameter statement</param>
		/// <returns>Sql Parameter statement</returns>
		public string GetSqlParameterStatement(ColumnSchema column)
		{
			return GetSqlParameterStatement(column, false);
		}
		
		/// <summary>
		/// Get a SqlParameter statement for a column
		/// </summary>
		/// <param name="column">Column for which to get the Sql parameter statement</param>
		/// <param name="isOutput">Is this an output parameter?</param>
		/// <returns>Sql Parameter statement</returns>
		public string GetSqlParameterStatement(ColumnSchema column, bool isOutput)
		{
			string param = "@" + column.Name + " " + column.NativeType;
			
			switch (column.DataType)
			{
				case DbType.Decimal:
				{
					param += "(" + column.Precision + ", " + column.Scale + ")";
					break;
				}
				case DbType.AnsiString:
				case DbType.AnsiStringFixedLength:
				case DbType.String:
				case DbType.StringFixedLength:
				{
					if (column.NativeType != "text" && column.NativeType != "ntext")
					{
						if (column.Size > 0)
						{
							param += "(" + column.Size + ")";
						}
					}
					break;
				}
			}
			
			if (isOutput)
			{
				param += " OUTPUT";
			}
			
			return param;
		}

		/// <summary>
		/// Parse the text of a stored procedure to retrieve any comment prior to the CREATE PROC construct
		/// </summary>
		/// <param name="commandText">Command Text of the procedure</param>
		/// <returns>The procedure header comment</returns>
		public string GetSqlProcedureComment(string commandText)
		{
			string comment = "";
			// Find anything upto the CREATE PROC statement
			Regex regex = new Regex(@"CREATE[\s]*PROC", RegexOptions.IgnoreCase);	
			comment = regex.Split(commandText)[0];
			//remove comment characters
			regex = new Regex(@"(-{2,})|(/\*)|(\*/)");
			comment = regex.Replace(comment, string.Empty);
			//trim and return
			return comment.Trim();
		}

		/// <summary>
		/// Get any in-line SQL comments on the same lines as parameters
		/// </summary>
		/// <param name="commandText">Command Text of the procedure</param>
		/// <returns>Hashtable of parameter comments, with parameter names as keys</returns>
		public Hashtable GetSqlParameterComments(string commandText)
		{
			string parameters = "";
			// Extracte the parameter definition section from the command text (between CREATE PROC and AS)
			Regex regex = new Regex(@"
				CREATE\s+						#Create followed by whitespace
				PROC(EDURE)*.*			#either proc or procedure
				(?<params>(.|\n)*) 	#Capture the match in params
				^(\s*AS)						#everything up to a line that starts with AS (preceding whitespace ignored)",
				RegexOptions.IgnoreCase | RegexOptions.IgnorePatternWhitespace | RegexOptions.Multiline);	
			parameters = regex.Match(commandText).Groups["params"].Value;
			Hashtable paramComments = new Hashtable();
			//loop through the matches and extract the parameter and the comment, ignoring duplicates
			regex = new Regex(@"
				(?<param>@\w*) 			#get each parameter (word starting with @)
				[^@]*								#ignore anything other than an @ sign
				--(?<comment>.*)		#until we get to the -- comment",
				RegexOptions.IgnoreCase | RegexOptions.IgnorePatternWhitespace | RegexOptions.Multiline);	
			//foreach (Match match in Regex.Matches(parameters, @"(?<param>@\w*)[^@]*--(?<comment>.*)"))
			foreach (Match match in regex.Matches(parameters))
			{
				if (!paramComments.ContainsKey(match.Groups["param"].Value))
					paramComments.Add(match.Groups["param"].Value, match.Groups["comment"].Value.Trim(new char[]{' ','\r','\n'}));
			}
			//return the hashtable
			return paramComments;
		}

		/// <summary>
		/// Convert database types to C# types
		/// </summary>
		/// <param name="dataType">Column or parameter data type</param>
		/// <param name="useNullableTypes">Are NullableTypes used?</param>
		/// <returns>The C# (rough) equivalent of the field's data type</returns>
		public string GetCSType(DbType dataType, bool useNullableTypes)
		{
			if (useNullableTypes)
				return GetNullableType(dataType);
			else
				return GetCSType(dataType);
		}

		/// <summary>
		/// Convert database types to C# types
		/// </summary>
		/// <param name="field">Column or parameter</param>
		/// <param name="useNullableTypes">Are NullableTypes used?</param>
		/// <returns>The C# (rough) equivalent of the field's data type</returns>
		public string GetCSType(DataObjectBase field, bool useNullableTypes)
		{
			return GetCSType(field.DataType, useNullableTypes);
		}

		/// <summary>
		/// Convert database types to C# types
		/// </summary>
		/// <param name="dataType">Column or parameter data type, as a string</param>
		/// <param name="useNullableTypes">Are NullableTypes used?</param>
		/// <returns>The C# (rough) equivalent of the field's data type</returns>
		public string GetCSType(string dataType, bool useNullableTypes)
		{
			try { return GetCSType((DbType)Enum.Parse(typeof(DbType), dataType), useNullableTypes); }
			catch { return "object"; }
		}

  /// <summary>
  /// Convert database types to programming language types
  /// </summary>
  /// <param name="dataType">Column or parameter data type</param>
  /// <returns>The programming language (rough) equivalent of the field's data type</returns>
	/// <remarks>Modified as per Jim Hughes' suggestion 
	/// (http://www.ericjsmith.net/codesmith/forum/default.aspx?f=9&p=2&m=1622#m2916)
	/// </remarks>
  public string GetCSType(DbType dataType)
  {
   switch (CodeTemplate.CodeTemplateInfo.TargetLanguage.ToLower())
   {
   case "vb":
    switch (dataType)
    {
     case DbType.AnsiString: return "String";
     case DbType.AnsiStringFixedLength: return "String";
     case DbType.Binary: return "Byte()";
     case DbType.Boolean: return "Boolean";
     case DbType.Byte: return "Byte";
     case DbType.Currency: return "Decimal";
     case DbType.Date: return "DateTime";
     case DbType.DateTime: return "DateTime";
     case DbType.Decimal: return "Decimal";
     case DbType.Double: return "Double";
     case DbType.Guid: return "Guid";
     case DbType.Int16: return "Int16";
     case DbType.Int32: return "Integer";
     case DbType.Int64: return "Int64";
     case DbType.Object: return "Object";
     case DbType.Single: return "Float";
     case DbType.String: return "String";
     case DbType.StringFixedLength: return "String";
     case DbType.Time: return "DateTime";
     case DbType.VarNumeric: return "Decimal";
      //the following won't be used
      //   case DbType.SByte: return "sbyte";
      //   case DbType.UInt16: return "ushort";
      //   case DbType.UInt32: return "uint";
      //   case DbType.UInt64: return "ulong";
     default: return "object";
    }
   case "c#":
    switch (dataType)
    {
     case DbType.AnsiString: return "string";
     case DbType.AnsiStringFixedLength: return "string";
     case DbType.Binary: return "byte[]";
     case DbType.Boolean: return "bool";
     case DbType.Byte: return "byte";
     case DbType.Currency: return "decimal";
     case DbType.Date: return "DateTime";
     case DbType.DateTime: return "DateTime";
     case DbType.Decimal: return "decimal";
     case DbType.Double: return "double";
     case DbType.Guid: return "Guid";
     case DbType.Int16: return "short";
     case DbType.Int32: return "int";
     case DbType.Int64: return "long";
     case DbType.Object: return "object";
     case DbType.Single: return "float";
     case DbType.String: return "string";
     case DbType.StringFixedLength: return "string";
     case DbType.Time: return "DateTime";
     case DbType.VarNumeric: return "decimal";
      //the following won't be used
      //   case DbType.SByte: return "sbyte";
      //   case DbType.UInt16: return "ushort";
      //   case DbType.UInt32: return "uint";
      //   case DbType.UInt64: return "ulong";
     default: return "object";
    }
   default: return "object";
   }
  }

		/// <summary>
		/// Convert database types to C# types
		/// </summary>
		/// <param name="field">Column or parameter</param>
		/// <returns>The C# (rough) equivalent of the field's data type</returns>
		public string GetCSType(DataObjectBase field)
		{
			return GetCSType(field.DataType);
		}

		/// <summary>
		/// Convert database types to C# types
		/// </summary>
		/// <param name="dataType">Column or parameter data type, as a string</param>
		/// <returns>The C# (rough) equivalent of the field's data type</returns>
		public string GetCSType(string dataType)
		{
			try { return GetCSType((DbType)Enum.Parse(typeof(DbType), dataType)); }
			catch { return "object"; }
		}

		/// <summary>
		/// Convert db types to NullableTypes
		/// </summary>
		/// <param name="dataType">Column or parameter data type</param>
		/// <returns>The NullableType (rough) equivalent of the field's data type</returns>
		public string GetNullableType(DbType dataType)
		{
			switch (dataType)
			{
				case DbType.AnsiString: return "NullableString";
				case DbType.AnsiStringFixedLength: return "NullableString";
				case DbType.Binary: return "NullableBinary";
				case DbType.Boolean: return "NullableBoolean";
				case DbType.Byte: return "NullableByte";
				case DbType.Currency: return "NullableDecimal";
				case DbType.Date: return "NullableDateTime";
				case DbType.DateTime: return "NullableDateTime";
				case DbType.Decimal: return "NullableDecimal";
				case DbType.Double: return "NullableDouble";
				case DbType.Guid: return "NullableGuid";
				case DbType.Int16: return "NullableInt16";
				case DbType.Int32: return "NullableInt32";
				case DbType.Int64: return "NullableInt64";
				case DbType.Object: return "object";
				case DbType.Single: return "NullableSingle";
				case DbType.String: return "NullableString";
				case DbType.StringFixedLength: return "NullableString";
				case DbType.Time: return "NullableDateTime";
				case DbType.VarNumeric: return "NullableDecimal";
					//the following won't be used
					//		case DbType.SByte: return "NullableSByte";
					//		case DbType.UInt16: return "NullableUShort";
					//		case DbType.UInt32: return "NullableUInt";
					//		case DbType.UInt64: return "NullableULong";
				default: return "object";
			}
		}


		/// <summary>
		/// Convert db types to NullableTypes
		/// </summary>
		/// <param name="field">Column or parameter</param>
		/// <returns>The NullableType (rough) equivalent of the field's data type</returns>
		public string GetNullableType(DataObjectBase field)
		{
			return GetNullableType(field.DataType);
		}

		/// <summary>
		/// Convert db types to NullableTypes
		/// </summary>
		/// <param name="dataType">Column or parameter data type, as a string</param>
		/// <returns>The NullableType (rough) equivalent of the field's data type</returns>
		public string GetNullableType(string dataType)
		{
			try { return GetNullableType((DbType)Enum.Parse(typeof(DbType), dataType)); }
			catch { return "object"; }
		}

		/// <summary>
		/// Get a default value for a given field's data type
		/// </summary>
		/// <param name="field">The field for which to get the default value</param>
		/// <returns>A string representation of the default value</returns>
		public string GetDefaultByType(DataObjectBase field)
		{
			return GetDefaultByType(field.DataType);
		}

		/// <summary>
		/// Get a default value for a given data type name
		/// </summary>
		/// <param name="dataType">String name of the data type for which to get the default value<</param>
		/// <returns>A string representation of the default value</returns>
		public string GetDefaultByType(string dataType)
		{
			try { return GetDefaultByType((DbType)Enum.Parse(typeof(DbType), dataType)); }
			catch { return "null"; }
		}

		/// <summary>
		/// Get a default value for a given data type
		/// </summary>
		/// <param name="dataType">Data type for which to get the default value</param>
		/// <returns>A string representation of the default value</returns>
		public string GetDefaultByType(DbType dataType)
		{
			switch (dataType)
			{
				case DbType.AnsiString: return "string.Empty";
				case DbType.AnsiStringFixedLength: return "string.Empty";
				case DbType.Binary: return "0";
				case DbType.Boolean: return "false";
				case DbType.Byte: return "0";
				case DbType.Currency: return "0";
				case DbType.Date: return "DateTime.MinValue";
				case DbType.DateTime: return "DateTime.MinValue";
				case DbType.Decimal: return "0";
				case DbType.Double: return "0";
				case DbType.Guid: return "0";
				case DbType.Int16: return "0";
				case DbType.Int32: return "0";
				case DbType.Int64: return "0";
				case DbType.Object: return "null";
				case DbType.Single: return "0";
				case DbType.String: return "0";
				case DbType.StringFixedLength: return "string.Empty";
				case DbType.Time: return "DateTime.MinValue"; 
				case DbType.VarNumeric: return "0";
					//the following won't be used
					//		case DbType.SByte: return "0";
					//		case DbType.UInt16: return "0";
					//		case DbType.UInt32: return "0";
					//		case DbType.UInt64: return "0";
				default: return "null";
			}
		}

		/// <summary>
		/// Get the Sql Data type of a column
		/// </summary>
		/// <param name="column">Column for which to get the type</param>
		/// <returns>String representing the SQL data type</returns>
		public string GetSqlDbType(DataObjectBase column)	
		{
			switch (column.NativeType)
			{
				case "bigint": return "BigInt";
				case "binary": return "Binary";
				case "bit": return "Bit";
				case "char": return "Char";
				case "datetime": return "DateTime";
				case "decimal": return "Decimal";
				case "float": return "Float";
				case "image": return "Image";
				case "int": return "Int";
				case "money": return "Money";
				case "nchar": return "NChar";
				case "ntext": return "NText";
				case "numeric": return "Decimal";
				case "nvarchar": return "NVarChar";
				case "real": return "Real";
				case "smalldatetime": return "SmallDateTime";
				case "smallint": return "SmallInt";
				case "smallmoney": return "SmallMoney";
				case "sql_variant": return "Variant";
				case "sysname": return "NChar";
				case "text": return "Text";
				case "timestamp": return "Timestamp";
				case "tinyint": return "TinyInt";
				case "uniqueidentifier": return "UniqueIdentifier";
				case "varbinary": return "VarBinary";
				case "varchar": return "VarChar";
				default: return "__UNKNOWN__" + column.NativeType;
			}
		}


#endregion

}





}